import json
import pandas as pd
import os
import sys
from sqlalchemy import create_engine, text, distinct, and_, select
from fastapi import FastAPI, HTTPException, Request
from fastapi.responses import FileResponse, HTMLResponse, RedirectResponse
from starlette.staticfiles import StaticFiles
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from typing import Optional, List
import uvicorn

# 添加父目录到路径，以便导入config和common模块
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from config import create_db_engine
from common.rule_flatten_handle import flatten_json, unflatten_json
import time

# 创建数据库引擎
engine = create_db_engine()

# 创建FastAPI应用
app = FastAPI(title="规则制作器 API", description="规则制作器后端服务")

# 添加CORS中间件
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# ===== 前端静态站点（vue/dist）托管 =====
# 将 vue 构建产物作为站点首页提供（若存在）；不影响 /api 与 /docs 等内置路由
_BASE_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
_DIST_DIR = os.path.join(_BASE_DIR, 'vue', 'dist')
_INDEX_HTML = os.path.join(_DIST_DIR, 'index.html')

if os.path.isdir(_DIST_DIR) and os.path.isfile(_INDEX_HTML):
    # 挂载静态资源目录，保持与构建产物中的路径一致
    for _name in ("css", "js", "img"):
        _sub = os.path.join(_DIST_DIR, _name)
        if os.path.isdir(_sub):
            app.mount(f"/{_name}", StaticFiles(directory=_sub), name=_name)

    # favicon 独立路由（无副作用）
    _favicon = os.path.join(_DIST_DIR, 'favicon.ico')
    if os.path.isfile(_favicon):
        @app.get('/favicon.ico')
        def _favicon_route():
            return FileResponse(_favicon)
else:
    # 未找到前端构建产物时，后续会在文件末尾注册根路由重定向到 /docs
    pass

# ===== 数据模型定义 =====

class RuleFlattenLocateReq(BaseModel):
    """规则查询、定位的请求模型"""
    description: Optional[str] = None
    info: Optional[str] = None
    note: Optional[str] = None
    isValid: Optional[str] = None
    field: Optional[str] = None
    operator: Optional[str] = None
    of: Optional[str] = None
    by: Optional[str] = None
    than: Optional[str] = None
    in_one_: Optional[str] = None
    price_lt: Optional[str] = None
    time_gte: Optional[str] = None
    time_lte: Optional[str] = None
    plus: Optional[str] = None
    plus_unit: Optional[str] = None
    plus_before_times: Optional[str] = None
    times: Optional[str] = None
    plus_after_times: Optional[str] = None
    time_span: Optional[str] = None
    time_unit: Optional[str] = None
    itemextra: Optional[str] = None
    itemscene: Optional[str] = None

class RuleMenuItemResp(BaseModel):
    """规则菜单项响应模型"""
    rule_id: str
    description: str

class RuleMenuResp(BaseModel):
    """规则菜单响应模型"""
    msg: str
    mark: str
    total: int
    data: List[RuleMenuItemResp]

class RuleApplyQueryReq(BaseModel):
    """规则应用、SQL查询请求模型"""
    ruleId: str
    hspAbbr: str

class SqlReq(BaseModel):
    """SQL查询、执行请求模型"""
    sql: str

# ===== 路由和服务函数 =====
@app.post('/api/rule_flatten/list', response_model=RuleMenuResp)
def get_rule_ids_by_properties(req: RuleFlattenLocateReq) -> RuleMenuResp:
    """
    根据请求的各个属性，从数据库查询匹配的rule_id列表
    使用RULE_FLATTEN表进行查询，支持模糊匹配
    """
    
    # 定义属性到key模式的映射
    property_key_mapping = {
        'description': 'description',
        'info': 'info',
        'note': 'note',
        'isValid': 'isValid',
        'field': 'field',
        'operator': 'operator',
        'of': 'of',
        'by': 'by',
        'than': 'than',
        'in_one_': 'in_one_',
        'price_lt': 'price_lt',
        'time_gte': 'time_gte',
        'time_lte': 'time_lte',
        'plus': 'plus',
        'plus_unit': 'plus_unit',
        'plus_before_times': 'plus_before_times',
        'times': 'times',
        'plus_after_times': 'plus_after_times',
        'time_span': 'time_span',
        'time_unit': 'time_unit',
        'itemextra': 'itemextra',
        'itemscene': 'itemscene'
    }
    
    # 收集所有匹配的rule_id
    all_rule_ids = set()
    
    with engine.connect() as conn:
        for prop_name, key_pattern in property_key_mapping.items():
            prop_value = getattr(req, prop_name, None)
            if prop_value is not None:
                # 查询匹配的rule_id - Oracle语法
                query = text("""
                    SELECT DISTINCT rule_id 
                    FROM RULE_FLATTEN 
                    WHERE key LIKE :key_pattern 
                    AND value LIKE :value_pattern
                """)
                
                result = conn.execute(query, {
                    "key_pattern": f"%{key_pattern}%",
                    "value_pattern": f"%{prop_value}%"
                })
                results = [row[0] for row in result]
                
                # 如果是第一个条件，初始化结果集
                if len(all_rule_ids) == 0:
                    all_rule_ids = set(results)
                else:
                    # 取交集，确保所有条件都满足
                    all_rule_ids = all_rule_ids.intersection(set(results))
                
                # 如果交集为空，直接返回空结果
                if len(all_rule_ids) == 0:
                    return RuleMenuResp(msg="未找到匹配的规则", mark="OK", total=0, data=[])
        
        # 如果 all_rule_ids 为空，说明没有任何条件被设置，返回空结果
        if len(all_rule_ids) == 0:
            return RuleMenuResp(msg="查询成功", mark="OK", total=0, data=[])
        
        # 如果 all_rule_ids 数量 > 50 个，则只返回50个
        if len(all_rule_ids) > 50:
            all_rule_ids = list(all_rule_ids)[:50]

        # 拿着这些rule_id查出 key='description'的记录，作为结果返回
        if len(all_rule_ids) > 0:
            # Oracle的IN查询语法
            placeholders = ','.join([f":id{i}" for i in range(len(all_rule_ids))])
            query = text(f"""
                SELECT rule_id, value 
                FROM RULE_FLATTEN 
                WHERE rule_id IN ({placeholders})
                AND key = 'description'
            """)
            
            # 准备参数
            params = {f"id{i}": rule_id for i, rule_id in enumerate(all_rule_ids)}
            
            result = conn.execute(query, params)
            results = result.fetchall()
            
            # 构建最终结果，结果是 rule_id 与 value(description) 共同构成的列表        
            result_items = [
                RuleMenuItemResp(rule_id=rule_id, description=value or "无描述") 
                for rule_id, value in results
            ]
        else:
            result_items = []
    
    return RuleMenuResp(
        msg="查询成功",
        mark="OK",
        total=len(result_items),
        data=result_items
    )

# 根据已知 rule_id 查询 rule_flatten
@app.post('/api/rule_flatten/query/json', response_model=dict)
def getRuleFlattenByRuleIds(data: dict) -> dict:
    ruleId = data.get("ruleId", None)
    if not ruleId:
        ruleId = data.get("rule_id", None)
    if not ruleId:
        return {
            "msg": "ruleId不能为空",
            "mark": "ERR",
            "total": 0,
            "data": []
        }

    with engine.connect() as conn:
        sql = text("""
            SELECT rule_id, key, type, value FROM rule_flatten WHERE rule_id = :rule_id
        """)
        result = conn.execute(sql, {"rule_id": ruleId})
        rows = result.fetchall()
        # 转换为 unflatten_json 需要的格式：[rule_id, key, type, value]
        flatData = []
        for row in rows:
            rule_id = row[0]
            key = row[1]
            data_type = row[2]
            value = row[3]
            flatData.append([rule_id, key, data_type, value])
        # 调用 unflatten_json 还原为 JSON 对象
        unflattened = unflatten_json(flatData)
        return {
            "msg": "查询成功",
            "mark": "OK",
            "total": len(unflattened),
            "data": unflattened
        }

# 上传对应 rule_id 的 json，转化为 flattened 格式，先删除对应id的旧数据再插入
@app.post('/api/rule_flatten/upload/by_rule_id/json', response_model=dict)
def uploadRuleFlattenByRuleId(data: list) -> dict:
    if not data or not isinstance(data, list) or len(data) == 0:
        return {
            "msg": "data不能为空且必须是非空列表",
            "mark": "ERR",  
            "total": 0,
        }
    
    # 先将 json_data 扁平化
    flat_items = flatten_json(data)
    if not flat_items or len(flat_items) == 0:
        return {
            "msg": "扁平化结果为空，上传失败",
            "mark": "ERR",
            "total": 0,
        }
    
    rule_id = flat_items[0][0]  # 所有条目的 rule_id 应该相同，取第一个即可

    # 删除旧数据
    with engine.connect() as conn:
        delete_sql = text("DELETE FROM rule_flatten WHERE rule_id = :rule_id")
        conn.execute(delete_sql, {"rule_id": rule_id})
        conn.commit()
        
        # 插入新数据
        insert_sql = text("""
            INSERT INTO rule_flatten (rule_id, key, type, value)
            VALUES (:rule_id, :key, :type, :value)
        """)
        for item in flat_items:
            conn.execute(insert_sql, {
                "rule_id": item[0],
                "key": item[1],
                "type": item[2],
                "value": item[3]
            })
        conn.commit()

    return {
        "msg": f"规则 {rule_id} 上传成功，插入 {len(flat_items)} 条记录",
        "mark": "OK",
        "total": len(flat_items)
    }

# 查 rule_apply 表中的所有 hsp_abbr，返回 hsp_abbr 列表
@app.post('/api/rule_apply/list/hsp_abbr', response_model=dict)
def getRuleApplyHspAbbrList() -> dict:
    with engine.connect() as conn:
        sql = text("SELECT DISTINCT hsp_abbr FROM rule_apply")
        result = conn.execute(sql)
        rows = result.fetchall()
        hsp_abbr_list = [row[0] for row in rows]
        return {
            "msg": "查询成功",
            "mark": "OK",
            "total": len(hsp_abbr_list),
            "data": hsp_abbr_list
        }

# 查某个特定 rule_id 下，key like '%_apply%'的所有记录
@app.post('/api/rule_apply/query/by_rule_id', response_model=dict)
def getRuleApplyByRuleId(request: RuleApplyQueryReq) -> dict:
    ruleId = request.ruleId
    hspAbbr = request.hspAbbr
    
    if not ruleId:
        return {
            "msg": "ruleId不能为空",
            "mark": "ERR",
            "total": 0,
            "data": []
        }

    if not hspAbbr:
        return {
            "msg": "hspAbbr不能为空",
            "mark": "ERR",
            "total": 0,
            "data": []
        }
    
    with engine.connect() as conn:
        sql = text("""
            SELECT rule_id, key, type, value 
            FROM rule_apply 
            WHERE rule_id = :rule_id and hsp_abbr = :hsp_abbr
            AND key LIKE :key_pattern
        """)
        result = conn.execute(sql, {
            "rule_id": ruleId,
            "hsp_abbr": hspAbbr,
            "key_pattern": "%_apply%"
        })
        rows = result.fetchall()
        # 转换为 unflatten_json 需要的格式：[rule_id, key, type, value]
        flatData = []
        for row in rows:
            rule_id = row[0]
            key = row[1]
            data_type = row[2]
            value = row[3]
            flatData.append([rule_id, key, data_type, value])
        flatDf = pd.DataFrame(flatData, columns=['rule_id', 'key', 'type', 'value'])
        
        # 对 flatData 进行简单转置，转换为表格
        # key当中最后一个 . 给split，后面的部分是字段        
        flatDf['field'] = flatDf['key'].apply(lambda x: x.split('.')[-1] if '.' in x else x)
        # key当中最后一个 . 给split以后，前面的部分，最后一个 # 给split，后面的部分是序号
        flatDf['ord'] = flatDf['key'].apply(lambda x: x.rsplit('.',1)[0])
        # key当中 _apply 给split以后，前面的部分路径
        flatDf['path'] = flatDf['key'].apply(lambda x: x.rsplit('#',1)[0] if '.' in x else x)
        # 对path进行去重，获得 pathList
        pathList = flatDf['path'].drop_duplicates().tolist()
        # 查询数据库，将 key like '%{path}%' 同时 t.key like '%field' 的记录查出来，做成字典，一个path对应一个value
        pathValueDict = {}
        for path in pathList:
            if 'filter' in path:
                # 如果 path 有 filter 在里面，就从这种方式中提取 field
                sql2 = text("""
                    SELECT value 
                    FROM rule_apply 
                    WHERE rule_id = :rule_id and hsp_abbr = :hsp_abbr
                    AND key LIKE :key_pattern AND key like '%%field'
                """)
                result2 = conn.execute(sql2, {
                    "rule_id": ruleId,
                    "hsp_abbr": hspAbbr,
                    "key_pattern": f"%{path.rsplit('.', 1)[0]}%"
                })
                rows2 = result2.fetchall()
                field_value = rows2[0][0] if rows2 and len(rows2) > 0 else None
            else:
                # 否则从_apply的字段中获取，将 key 通过 .来split，判断哪一段里面有 _apply
                field_value = path.rsplit('.',1)[-1] if '.' in path else path

            pathValueDict[path] = field_value
        
        # 构造成为 applyData
        applyData = []
        for path in pathList:
            # 获取当前path的数据
            path_data = flatDf[flatDf['path'] == path]
            
            # 如果有数据，进行pivot转换
            if not path_data.empty:
                pivot_data = path_data.pivot(
                    index='ord', columns='field', values='value'
                ).reset_index().to_dict(orient='records')
            else:
                pivot_data = []
            
            applyData.append({
                "path": path,
                "field": pathValueDict.get(path, None),
                "apply": pivot_data
            })

        return {
            "msg": "查询成功",
            "mark": "OK",
            "total": len(applyData),
            "data": applyData
        }

# 查某个特定 rule_id 下，key like '%sqlList'的所有记录
@app.post('/api/rule_apply/query_sql/by_rule_id', response_model=dict)
def getSqlByRuleId(request: RuleApplyQueryReq) -> dict:
    ruleId = request.ruleId
    if not ruleId:
        return {
            "msg": "ruleId不能为空",
            "mark": "ERR",
            "total": 0,
            "data": []
        }
    hspAbbr = request.hspAbbr
    if not hspAbbr:
        return {
            "msg": "hspAbbr不能为空",
            "mark": "ERR",
            "total": 0,
            "data": []
        }

    with engine.connect() as conn:
        sql = text("""
            SELECT key, sql
            FROM rule_apply 
            WHERE rule_id = :rule_id AND hsp_abbr = :hsp_abbr
            AND key LIKE :key_pattern
        """)
        params = {
            "rule_id": request.ruleId,
            "hsp_abbr": request.hspAbbr,
            "key_pattern": "%sqlList%"
        }
        # # 打印替换参数后的 sql
        # sql_str = str(sql)
        # print(f"原始SQL: {sql_str}")
        # print(f"参数: {params}")
        # # 手动替换参数显示完整SQL（仅用于调试）
        # debug_sql = sql_str.replace(":rule_id", f"'{request.ruleId}'")
        # debug_sql = debug_sql.replace(":hsp_abbr", f"'{request.hspAbbr}'")
        # debug_sql = debug_sql.replace(":key_pattern", "'%sqlList%'")
        # print(f"执行的SQL语句: {debug_sql}")
        result = conn.execute(sql, params)

        rows = result.fetchall()
        # 获得结果。其中 sql 是 CLOB 类型，需要转换为字符串
        results = []
        for row in rows:
            key = row[0]
            sql_clob = row[1]
            sql_str = str(sql_clob) if sql_clob is not None else None
            results.append({
                "key": key,
                "sql": sql_str
            })
        if len(results) == 0:
            return {
                "msg": "未找到匹配的SQL",
                "mark": "OK",
                "total": 0,
                "data": []
            }
        dfResults = pd.DataFrame(results)
        dfResults['ord'] = dfResults['key'].apply(lambda x: int(x.rsplit('#',1)[-1]))
        dfResults['path'] = dfResults['key'].apply(lambda x: x.rsplit('.',1)[0] if '.' in x else x)
        # 获得 key 去重列表
        pathList = dfResults['path'].drop_duplicates().tolist()
        sqlData = []
        for path in pathList:
            path_data = dfResults[dfResults['path'] == path]
            sql_list = path_data.sort_values(by='ord')['sql'].tolist()
            sqlData.append({
                "path": path,
                "sqlList": sql_list
            })
        
        # 每个 key 的 sqlList 增加6个临时表的 TRUNCATE 语句
        for item in sqlData:
            truncate_sqls = [f"TRUNCATE TABLE vlt_tmp_{tblName}" for tblName in ['a', 'b', 'b2', 'b3', 'b4', 'b5']]
            item['sqlList'] = truncate_sqls + item['sqlList']
        
        return {
            "msg": "查询成功",
            "mark": "OK",
            "total": len(sqlData),
            "data": sqlData
        }

# 执行SQL语句，返回结果
@app.post('/api/rule_apply/exec_sql', response_model=dict)
def execSql(request: SqlReq) -> dict:
    sql = request.sql
    if not sql:
        return {
            "msg": "SQL不能为空",
            "mark": "ERR",
            "data": None
        }

    with engine.connect() as conn:
        result = conn.execute(text(sql))
        # 执行是指插入、更新、删除等操作，没有结果，完成后返回受影响行数
        try:
            t0 = time.time()
            affectedCount = result.rowcount
            conn.commit()
            # 查询 vlt_tmp_a, vlt_tmp_b, vlt_tmp_b2 ... vlt_tmp_b5 这6个表的记录数
            for tblName in ['vlt_tmp_a', 'vlt_tmp_b', 'vlt_tmp_b2', 'vlt_tmp_b3', 'vlt_tmp_b4', 'vlt_tmp_b5']:
                countResult = conn.execute(text(f"SELECT COUNT(*) FROM {tblName}"))
                countRow = countResult.fetchone()
                count = countRow[0] if countRow else 0
                # 将表名和记录数加入结果
                if 'tableCounts' not in locals():
                    tableCounts = {}
                tableCounts[tblName] = count

            return {
                "msg": "执行成功",
                "mark": "OK",
                "data": {
                    "rowCount": affectedCount,
                    "tableCounts": tableCounts,
                    "execTimeSec": round(time.time() - t0, 3)
                }
            }
        except Exception as e:
            return {
                "msg": f"执行失败: {e}",
                "mark": "ERR",
                "data": None
            }

# 查询SQL语句，返回结果
@app.post('/api/rule_apply/query_sql_exec', response_model=dict)
def querySqlExec(request: SqlReq) -> dict:
    sql = request.sql
    if not sql:
        return {
            "msg": "SQL不能为空",
            "mark": "ERR",
            "data": None
        }

    with engine.connect() as conn:
        try:
            result = conn.execute(text(sql))
            rows = result.fetchall()
            # 获取列名
            columns = result.keys()
            # 将结果转换为列表字典形式
            data = [dict(zip(columns, row)) for row in rows]
            length = len(data)
            # 仅展示前1000条
            if length > 1000:
                data = data[:1000]
            return {
                "msg": "查询成功",
                "mark": "OK",
                "total": length,
                "data": data
            }
        except Exception as e:
            return {
                "msg": f"查询失败: {e}",
                "mark": "ERR",
                "total": 0,
                "data": None
            }

# ===== 启动配置 =====
if __name__ == "__main__":
    try:
        import uvicorn
        uvicorn.run(app, host="0.0.0.0", port=8000, reload=True)
    except ImportError:
        print("uvicorn未安装，请先安装: pip install uvicorn")
        print("或者使用其他方式启动FastAPI应用")

# 在所有 API 路由注册完成后，最后挂载根路径与 SPA fallback，避免拦截 /api 与 /docs
if os.path.isdir(_DIST_DIR) and os.path.isfile(_INDEX_HTML):
    @app.get('/', response_class=HTMLResponse)
    def _root_index():
        return FileResponse(_INDEX_HTML)

    @app.get('/{full_path:path}', response_class=HTMLResponse)
    async def _spa_fallback(full_path: str, request: Request):
        # 放行 API 与文档路径
        if full_path.startswith(('api/', 'docs', 'redoc', 'openapi.json')):
            raise HTTPException(status_code=404, detail="Not Found")
        return FileResponse(_INDEX_HTML)
else:
    @app.get('/')
    def _root_redirect():
        return RedirectResponse(url='/docs')
